/*
	exec sp_backlog_report_proc N'0',N'0',N'0',N'0',N'0',N'ALL',1,N'reqDockDate',N'21/04/2008:16:31:52',N'byMonth',N'XLREPORT'
*/
alter PROC sp_backlog_report_proc

@PRODUCT_ID 		VARCHAR(200),
@SO_NUMBER 		VARCHAR(15),
@CUSTOMER_ID 		VARCHAR(200),
@SALES_PERSON 		VARCHAR(500),
@ASOFDATE 		VARCHAR(15),
@BACKLOG_TYPE 		VARCHAR(10),
@ORDER_TYPE		INT,
@CATEGORY		VARCHAR(100),
@DEFAULTPROMPT  	VARCHAR(50),
@BYPERIOD 		VARCHAR(20),
@REPORT_TYPE		VARCHAR(20),
@REGION			VARCHAR(20),
@ENDUSER		VARCHAR(100)
 
AS
BEGIN
	CREATE TABLE #BACKLOG_TABLE (	
				[CUSTOMER NAME] VARCHAR(50),[CUSTOMER NUMBER] VARCHAR(50), [SALESORDER NUMBER] VARCHAR(50), 
				[LINEITEM NO] INT,[REVLEVEL] INT ,[CUSTOMER PO] VARCHAR(50),
				[SO BOOKING DATE] DATETIME, [SO BOOKING DATE WORK WEEK NO] INT,[SO BOOKING DATE QUARTER] INT,
				[SALES PERSON] VARCHAR(50),[REGION REP] VARCHAR(50), [END CUSTOMER] VARCHAR(50),
				[END USER] VARCHAR(50),[PAY TERMS] VARCHAR(50),[DELIVERY TERMS] VARCHAR(50),[SHIPPING METHOD] VARCHAR(50),
				[REQUESTED DATE] DATETIME,[REQUESTED DATE WORK WEEK NO] INT,[REQUESTED DATE QUARTER] INT, 
				[SCHEDULE DATE] DATETIME,[SCHEDULE DATE WORK WEEK NO] INT,[SCHEDULE DATE QUARTER] INT,
				[REVISED DATE] DATETIME,[REVISED DATE WORK WEEK NO] INT,[REVISED DATE QUARTER] INT, 
				[PART_NUMBER] VARCHAR(50), [REQUESTED QYT] INT,[UNIT COST] FLOAT ,STATUS VARCHAR(20),
				IS_EXPEDITE INT,[PERIODICALLY VALUE] INT,[REMANING QTY] INT,[SALES HOLD] VARCHAR(10), 
				[MARKETING HOLD] VARCHAR(10),[MANUFACTURING HOLD] VARCHAR(10),[FINANCE HOLD] VARCHAR(10),
				ACTUAL_YEAR INT,COOL_IS_CANCEL int,ISCONSIGNMENT BIT,[DATE ORDER] DATETIME )

	IF @ASOFDATE <> '0' 
	BEGIN
		--SET @TEMPDATE = CONVERT(DATETIME, @ASOFDATE)
		EXEC sp_backlog_asofdate_report_proc @PRODUCT_ID, @SO_NUMBER, @CUSTOMER_ID, @SALES_PERSON, @ASOFDATE, @BACKLOG_TYPE, @CATEGORY ,@ORDER_TYPE,@BYPERIOD,@REPORT_TYPE
	END
	ELSE
	BEGIN
		IF @BACKLOG_TYPE = 'ALL'
		BEGIN
			SET @BACKLOG_TYPE = 0
		END
		ELSE IF @BACKLOG_TYPE = 'OPEN'
		BEGIN
			SET @BACKLOG_TYPE = 'APPROVED'
		END

		DECLARE @BACKLOGSELECTSTRING 		NVARCHAR(4000)
		DECLARE @BACKLOGFROMSTRING 		NVARCHAR(4000)
		--DECLARE @PARAMDEFINITION 	NVARCHAR(500)
	
		--SET @PARAMDEFINITION = N'@PRM_BY_PERIOD VARCHAR(50),@PRM_CATEGORY VARCHAR(50),@PRM_ORDER_TYPE INT, @PRM_PRODUCT_ID VARCHAR(50),@PRM_SO_NUMBER VARCHAR(50),@PRM_CUSTOMER_ID VARCHAR(50),@PRM_SALES_PERSON VARCHAR(50),@PRM_BACKLOG_TYPE VARCHAR(20)'
		SET @BACKLOGSELECTSTRING = N' INSERT INTO #BACKLOG_TABLE 
		select 	
			C.c_name, c.c_number, so.salesorder_no,cool_line_item_no,
			( select isnull(max(ChangeNo),0) from change_sales_order where salesorder_id = SO.SALESORDER_ID and CSO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDER_TYPE) +''') Rev_level, 
	   		so.so_customer_PO_number, so.SO_Booking_Date, SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO,
			so.so_sales_person, so.Regional_Rep,
			(CASE 	
				WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) THEN  C_NAME 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 		END),
			(CASE 
				WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) THEN 
					(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) THEN C_NAME 
					 	ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END)
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END),
			SO.SHIPPING_TERMS,SO.DELIVERY_TERMS,SO_SHIPPING_METHOD, 
	   		cool.cool_requested_date,COOL.REQUESTED_DATE_WW_NO,COOL.REQUESTED_DATE_QTR_NO,
			ISNULL(cool.cool_schedule_date,cool.cool_requested_date), 
			ISNULL(cool.SCHEDULE_DATE_WW_NO,cool.REQUESTED_DATE_WW_NO),
			ISNULL(cool.SCHEDULE_DATE_QTR_NO,cool.REQUESTED_DATE_QTR_NO),
			ISNULL(ISNULL(COOL.COOL_REVISED_DATE,COOL.COOL_SCHEDULE_DATE),ISNULL(COOL.COOL_REVISED_DATE,cool.cool_requested_date)),
			ISNULL(ISNULL(COOL.REVISED_DATE_WW_NO,COOL.SCHEDULE_DATE_WW_NO),ISNULL(COOL.REVISED_DATE_WW_NO,cool.REQUESTED_DATE_WW_NO)),
			ISNULL(ISNULL(COOL.REVISED_DATE_QTR_NO,COOL.SCHEDULE_DATE_QTR_NO),ISNULL(COOL.REVISED_DATE_QTR_NO,cool.REQUESTED_DATE_QTR_NO)),
			p.part_number, cool.cool_qty, 
	   		cool.cool_unit_cost, cool.COOL_Status ,cool.COOL_Is_Expedite,
			( CASE	WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN
				 ( CASE WHEN '''+ @BYPERIOD +''' = ''byMonth'' THEN DATEPART(m, COOL.COOL_REQUESTED_DATE) 
					WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN COOL.REQUESTED_DATE_QTR_NO
					/*WHEN '''+ @BYPERIOD +''' = ''byWeek'' THEN COOL.REQUESTED_DATE_WW_NO*/
					ELSE DATEPART(yyyy, COOL.COOL_REQUESTED_DATE)
				  END )
				WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN
				 ( CASE WHEN '''+ @BYPERIOD +''' = ''byMonth'' THEN DATEPART(m, COOL.COOL_SCHEDULE_DATE) 
					WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN COOL.SCHEDULE_DATE_QTR_NO
					/*WHEN '''+ @BYPERIOD +''' = ''byWeek'' THEN COOL.SCHEDULE_DATE_WW_NO*/
					ELSE DATEPART(yyyy, COOL.COOL_SCHEDULE_DATE)
				  END )
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN
				 ( CASE WHEN '''+ @BYPERIOD +''' = ''byMonth'' THEN DATEPART(m, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE)) 
					WHEN '''+ @BYPERIOD +''' = ''byQuarter'' THEN ISNULL(COOL.REVISED_DATE_QTR_NO,COOL.SCHEDULE_DATE_QTR_NO)
					/*WHEN '''+ @BYPERIOD +''' = ''byWeek'' THEN ISNULL(COOL.REVISED_DATE_WW_NO,COOL.SCHEDULE_DATE_WW_NO)*/
					ELSE DATEPART(yyyy, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE))
				  END )
				WHEN '''+ @CATEGORY +''' = ''salesOrder'' OR '''+ @CATEGORY +''' = ''partNumber'' THEN DATEPART(yyyy, SO.SO_BOOKING_DATE)
			END ) AS PERIODICALLY_VALUE, '
	SET @BACKLOGFROMSTRING = N'		
			(COOL.COOL_QTY - ( SELECT ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = ''SHIPPED'' )) AS REM_QTY,
			( CASE WHEN  cool.COOL_Status = ''HOLD'' THEN ( select ( case when (hold_reason_code is not null and hold_reason_code = ''0'' ) then ''X''
					else hold_reason_code end )from sales_hold_table where hold_by_group = ''SALES'' and lineitem_id = COOL.LINEITEM_ID )
			ELSE NULL END ) SALES_HOLD,
			( CASE WHEN  cool.COOL_Status = ''HOLD'' THEN ( select ( case when (hold_reason_code is not null and hold_reason_code = ''0'' ) then ''X''
					else hold_reason_code end )from sales_hold_table where hold_by_group = ''MARKETING'' and lineitem_id = COOL.LINEITEM_ID )
			ELSE NULL END ) MARKETING_HOLD,
			( CASE WHEN  cool.COOL_Status = ''HOLD'' THEN ( select ( case when ( hold_reason_code is not null and hold_reason_code = ''0'' ) then ''X''
					else hold_reason_code end )from sales_hold_table where hold_by_group = ''OPERATIONS'' and lineitem_id = COOL.LINEITEM_ID )
			ELSE NULL END ) OPERATIONS_HOLD,
			( CASE WHEN  cool.COOL_Status = ''HOLD'' THEN ( select ( case when ( hold_reason_code is not null and hold_reason_code = ''0'' ) then ''X''
					else hold_reason_code end )from sales_hold_table where hold_by_group = ''FINANCE'' and lineitem_id = COOL.LINEITEM_ID )
			ELSE NULL END ) FINANCE_HOLD, 
		
			(CASE	WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN DATEPART(yyyy, COOL.COOL_REQUESTED_DATE)
				WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN DATEPART(yyyy, COOL.COOL_SCHEDULE_DATE)
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN DATEPART(yyyy, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE))
				ELSE DATEPART(yyyy, SO.SO_BOOKING_DATE)
			END),cool_is_cancel,SO_IS_CONSIGNMENT, 
			(CASE	WHEN '''+ @CATEGORY +''' = ''reqDockDate'' THEN COOL.COOL_REQUESTED_DATE
				WHEN '''+ @CATEGORY +''' = ''scheduleDate'' THEN COOL.COOL_SCHEDULE_DATE
				WHEN '''+ @CATEGORY +''' = ''revDate'' THEN ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE)
				ELSE SO.SO_BOOKING_DATE
			END)
		from 	
			CUSTOMER_OPEN_ORDER_LINEITEM COOL INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
			INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID
			INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID			
	   	where 
			cool.COOL_Status<> ''SHIPPED'' '
	
		IF ( @ORDER_TYPE IS NOT NULL AND @ORDER_TYPE <> 2 )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND ( ( SO_IS_CONSIGNMENT = 0 ) OR ( C_IS_Consignee = 1 and SO_IS_CONSIGNMENT = 1 ) ) AND SO.SO_IS_SAMPLE = '''+ CONVERT(VARCHAR,@ORDER_TYPE) +''''
		END
		ELSE IF ( @ORDER_TYPE IS NOT NULL AND @ORDER_TYPE = 2 )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND (  C_IS_Consignee = 0 and SO_IS_CONSIGNMENT = 1 ) '
		END

		IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.CUSTOMER_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMER_ID +'''))'
		END	
		IF ( @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND COOL.PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
		END
		IF ( @SO_NUMBER IS NOT NULL AND @SO_NUMBER <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.SALESORDER_NO = '''+ @SO_NUMBER +''''
		END
		IF ( @SALES_PERSON IS NOT NULL AND @SALES_PERSON <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND SO.SO_SALES_PERSON  IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALES_PERSON +'''))'
		END
		IF ( @BACKLOG_TYPE IS NOT NULL AND @BACKLOG_TYPE <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND cool.COOL_Status = '''+ @BACKLOG_TYPE +''''
		END
		IF ( @REGION IS NOT NULL AND @REGION <> '0' )
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C.C_Region ) = '''+ @REGION +''''
		END		
		IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
		BEGIN
			SET @BACKLOGFROMSTRING = @BACKLOGFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
		END
		 --select * from #BACKLOG_TABLE
		--EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_BY_PERIOD = @BY_PERIOD,@PRM_CATEGORY = @CATEGORY,@PRM_ORDER_TYPE = @ORDER_TYPE,@PRM_PRODUCT_ID = @PRODUCT_ID,@PRM_SO_NUMBER = @SO_NUMBER,@PRM_CUSTOMER_ID = @CUSTOMER_ID,@PRM_SALES_PERSON = @SALES_PERSON,@PRM_BACKLOG_TYPE = @BACKLOG_TYPE
		--print (@BACKLOGSELECTSTRING)
		--print (@BACKLOGFROMSTRING)
		EXEC (@BACKLOGSELECTSTRING+@BACKLOGFROMSTRING)

		IF @REPORT_TYPE = 'XLREPORT'
		BEGIN
			IF @CATEGORY = 'partNumber'
			BEGIN
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
					[SALESORDER NUMBER],[LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER], 
					[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
					[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER],
					[SCHEDULE DATE] ,[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
					[REVISED DATE] ,[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER], 
					[PART_NUMBER] , [REQUESTED QYT] ,[UNIT COST] ,[REMANING QTY],
					( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE] --,[PERIODICALLY VALUE]
			
				FROM #BACKLOG_TABLE
				order by [PART_NUMBER],[CUSTOMER NAME]
			END
			ELSE IF @CATEGORY = 'salesOrder'
			BEGIN
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
					[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER], 
					[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
					[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER],
					[SCHEDULE DATE] ,[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
					[REVISED DATE] ,[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER], 
					[PART_NUMBER] , [REQUESTED QYT] ,[UNIT COST] ,[REMANING QTY],
					( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE]--,[PERIODICALLY VALUE]
			
				FROM #BACKLOG_TABLE
				order by [SALESORDER NUMBER],[CUSTOMER NAME]
			END
			ELSE
			BEGIN
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],
					[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE],[SO BOOKING DATE WORK WEEK NO],[SO BOOKING DATE QUARTER], 
					[SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],
					[REQUESTED DATE],[REQUESTED DATE WORK WEEK NO],[REQUESTED DATE QUARTER], 
					[SCHEDULE DATE] ,[SCHEDULE DATE WORK WEEK NO],[SCHEDULE DATE QUARTER], 
					[REVISED DATE] ,[REVISED DATE WORK WEEK NO],[REVISED DATE QUARTER],  
					[PART_NUMBER] , [REQUESTED QYT] ,[UNIT COST] ,[REMANING QTY],
					( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE] --,[PERIODICALLY VALUE]
			
				FROM #BACKLOG_TABLE
				order by [PERIODICALLY VALUE],[DATE ORDER]  
			END
		END
		ELSE
		BEGIN
			/*IF @CATEGORY = 'partNumber'
			BEGIN
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
					[REQUESTED DATE], [SCHEDULE DATE] , [REVISED DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
					[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
					(CASE  	
						WHEN STATUS = 'APPROVED'  THEN 'Open Orders'
						ELSE 
							( CASE 
								WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
						   		ELSE 'Designated Holds' 
			 				END) 
					END) AS HOLD_TYPE,cool_is_cancel,ISCONSIGNMENT
			
				FROM #BACKLOG_TABLE
				order by [DATE ORDER] DESC 
			END
			ELSE IF @CATEGORY = 'salesOrder'
			BEGIN
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
					[REQUESTED DATE], [SCHEDULE DATE] , [REVISED DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
					[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
					(CASE  	
						WHEN STATUS = 'APPROVED'  THEN 'Open Orders'
						ELSE 
							( CASE 
								WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
						   		ELSE 'Designated Holds' 
			 				END) 
					END) AS HOLD_TYPE,cool_is_cancel,ISCONSIGNMENT
			
				FROM #BACKLOG_TABLE
				order by [DATE ORDER] DESC 
			END
			ELSE
			BEGIN*/
				select 
					[CUSTOMER NAME],[CUSTOMER NUMBER], [END CUSTOMER],[END USER],[SALESORDER NUMBER], [LINEITEM NO],[REVLEVEL]  ,[CUSTOMER PO] , 
					[SO BOOKING DATE], [SALES HOLD] , [MARKETING HOLD] ,[MANUFACTURING HOLD] ,[FINANCE HOLD],
					[PAY TERMS],[DELIVERY TERMS] ,[SHIPPING METHOD],IS_EXPEDITE,
					[REQUESTED DATE], [SCHEDULE DATE] , [REVISED DATE] , [PART_NUMBER] , [REQUESTED QYT] , 
					[UNIT COST] ,[REMANING QTY],( [REMANING QTY] * [UNIT COST] ) as [EXTENDED PRICE],isnull([PERIODICALLY VALUE],0) [PERIODICALLY VALUE],ACTUAL_YEAR,
					(CASE  	
						WHEN STATUS = 'APPROVED'  THEN 'Open Orders'
						ELSE 
							( CASE 
								WHEN ( [SALES HOLD] = 'X' OR [MARKETING HOLD] = 'X' OR [MANUFACTURING HOLD] = 'X' OR [FINANCE HOLD] = 'X' ) THEN 'General Holds'
						   		ELSE 'Designated Holds' 
			 				END) 
					END) AS HOLD_TYPE,cool_is_cancel,ISCONSIGNMENT
			
				FROM #BACKLOG_TABLE
				order by [DATE ORDER]  
			--END
		END
--select * from #BACKLOG_TABLE
	END
END

